/*
               File: reorg
        Description: Table Manager
             Author: DeKlarit - Language C# - Generator Version 10_1_3-31907 - Specifier Version 10_
       Program type: Callable routine
          Main DBMS: Microsoft SqlServer
*/
using System;
using System.Collections;
using Deklarit.Utils;
using System.Threading;
using Deklarit.Reorganization;
using DeklaritModel.Reorg;
using System.Data;
using System.Data.SqlClient;
using System.Data.Common;
using Deklarit.Data;
using System.Xml.Serialization;
using System.Data.SqlTypes;
namespace DeklaritModel.Reorg {
   public class reorg
   {
      public event ReorganizationMessageEventHandler messageHandler;

      public reorg( DataStore ds ,
                    ReorganizationMessageEventHandler messageHandler )
      {
         this.dsDefault = ds ;
         this.messageHandler = messageHandler ;
         connDefault = dsDefault.GetReadWriteConnection() ;
         Initialize( ) ;
      }

      public void AddMsg( string message )
      {
         if ( this.messageHandler != null )
         {
            this.messageHandler( this, new ReorganizationMessageEventArgs( message)) ;
         }
      }

      public void execute( )
      {
         executePrivate();
      }

      void executePrivate( )
      {
         if ( PreviousCheck() )
         {
            ExecuteReorganization( ) ;
         }
      }

      private void FirstActions( )
      {
         // Load data into tables.
      }

      public void ReorganizePais( )
      {
         String cmdBuffer ;
         AddMsg( string.Format(resourceManager.GetString("GXM_fileupdate"), new   object[]  {"Pais", ""}) );
         AddMsg( string.Format(resourceManager.GetString("GXM_fileren"), new   object[]  {"GXA0005", "Pais"}) );
         // Indices for table Pais
         AddMsg( string.Format(resourceManager.GetString("GXM_creaindx"), new   object[]  {"IPais"}) );
         try
         {
            cmdBuffer=" CREATE TABLE [GXA0005] ([PaisId] smallint NOT NULL  IDENTITY(1,1), [PaisNombre] char(20) NOT NULL , [PaisMoneda] char(2) NOT NULL )  "
            ;
            RGZ = connDefault.GetCommand(cmdBuffer,false);
            RGZ.IDbCommand.CommandTimeout = 0;
            RGZ.ExecuteStmt() ;
         }
         catch ( Exception ex )
         {
            DropTableConstraints( "[GXA0005]") ;
            cmdBuffer=" DROP TABLE [GXA0005] "
            ;
            RGZ = connDefault.GetCommand(cmdBuffer,false);
            RGZ.IDbCommand.CommandTimeout = 0;
            RGZ.ErrorMask = RGZ.ErrorMask  |  ErrorMask.FileNotFound;
            RGZ.ExecuteStmt() ;
            cmdBuffer=" CREATE TABLE [GXA0005] ([PaisId] smallint NOT NULL  IDENTITY(1,1), [PaisNombre] char(20) NOT NULL , [PaisMoneda] char(2) NOT NULL )  "
            ;
            RGZ = connDefault.GetCommand(cmdBuffer,false);
            RGZ.IDbCommand.CommandTimeout = 0;
            RGZ.ExecuteStmt() ;
         }
         cmdBuffer=" SET IDENTITY_INSERT [GXA0005] ON "
         ;
         RGZ = connDefault.GetCommand(cmdBuffer,false);
         RGZ.IDbCommand.CommandTimeout = 0;
         RGZ.ExecuteStmt() ;
         cmdBuffer=" INSERT INTO [GXA0005] ([PaisId], [PaisNombre], [PaisMoneda]) SELECT [PaisId], [PaisNombre], [PaisMoneda] FROM [Pais] T1 "
         ;
         RGZ = connDefault.GetCommand(cmdBuffer,false);
         RGZ.IDbCommand.CommandTimeout = 0;
         RGZ.ExecuteStmt() ;
         cmdBuffer=" SET IDENTITY_INSERT [GXA0005] OFF "
         ;
         RGZ = connDefault.GetCommand(cmdBuffer,false);
         RGZ.IDbCommand.CommandTimeout = 0;
         RGZ.ExecuteStmt() ;
         DropTableConstraints( "[Pais]") ;
         cmdBuffer=" DROP TABLE [Pais] "
         ;
         RGZ = connDefault.GetCommand(cmdBuffer,false);
         RGZ.IDbCommand.CommandTimeout = 0;
         RGZ.ErrorMask = RGZ.ErrorMask  |  ErrorMask.FileNotFound;
         RGZ.ExecuteStmt() ;
         RGZ = connDefault.GetNewCommand("sp_rename", false);
         RGZ.IDbCommand.CommandType = CommandType.StoredProcedure;
         RGZ.IDbCommand.Parameters.Add(  dsDefault.GetDbParameter( "@objname", "[GXA0005]"));
         RGZ.IDbCommand.Parameters.Add(  dsDefault.GetDbParameter( "@newname", "Pais"));
         RGZ.ExecuteStmt();
         RGZ.IDbCommand.CommandType = CommandType.Text;
         cmdBuffer=" ALTER TABLE [Pais] ADD PRIMARY KEY([PaisId]) "
         ;
         RGZ = connDefault.GetCommand(cmdBuffer,false);
         RGZ.IDbCommand.CommandTimeout = 0;
         RGZ.ExecuteStmt() ;
      }

      public void RIClientePais( )
      {
         String cmdBuffer ;
         AddMsg( string.Format(resourceManager.GetString("GXM_refintcrea"), new   object[]  {"[ICliente1]"}) );
         try
         {
            cmdBuffer=" ALTER TABLE [Cliente] ADD CONSTRAINT [ICliente1] FOREIGN KEY ([PaisId]) REFERENCES [Pais] ([PaisId]) "
            ;
            RGZ = connDefault.GetCommand(cmdBuffer,false);
            RGZ.IDbCommand.CommandTimeout = 0;
            RGZ.ExecuteStmt() ;
         }
         catch ( Exception ex )
         {
            cmdBuffer=" ALTER TABLE [Cliente] DROP CONSTRAINT [ICliente1] "
            ;
            RGZ = connDefault.GetCommand(cmdBuffer,false);
            RGZ.IDbCommand.CommandTimeout = 0;
            RGZ.ErrorMask = RGZ.ErrorMask  |  ErrorMask.FileNotFound;
            RGZ.ExecuteStmt() ;
            cmdBuffer=" ALTER TABLE [Cliente] ADD CONSTRAINT [ICliente1] FOREIGN KEY ([PaisId]) REFERENCES [Pais] ([PaisId]) "
            ;
            RGZ = connDefault.GetCommand(cmdBuffer,false);
            RGZ.IDbCommand.CommandTimeout = 0;
            RGZ.ExecuteStmt() ;
         }
      }

      public void RICiudadPais( )
      {
         String cmdBuffer ;
         AddMsg( string.Format(resourceManager.GetString("GXM_refintcrea"), new   object[]  {"[ICiudad1]"}) );
         try
         {
            cmdBuffer=" ALTER TABLE [Ciudad] ADD CONSTRAINT [ICiudad1] FOREIGN KEY ([PaisId]) REFERENCES [Pais] ([PaisId]) "
            ;
            RGZ = connDefault.GetCommand(cmdBuffer,false);
            RGZ.IDbCommand.CommandTimeout = 0;
            RGZ.ExecuteStmt() ;
         }
         catch ( Exception ex )
         {
            cmdBuffer=" ALTER TABLE [Ciudad] DROP CONSTRAINT [ICiudad1] "
            ;
            RGZ = connDefault.GetCommand(cmdBuffer,false);
            RGZ.IDbCommand.CommandTimeout = 0;
            RGZ.ErrorMask = RGZ.ErrorMask  |  ErrorMask.FileNotFound;
            RGZ.ExecuteStmt() ;
            cmdBuffer=" ALTER TABLE [Ciudad] ADD CONSTRAINT [ICiudad1] FOREIGN KEY ([PaisId]) REFERENCES [Pais] ([PaisId]) "
            ;
            RGZ = connDefault.GetCommand(cmdBuffer,false);
            RGZ.IDbCommand.CommandTimeout = 0;
            RGZ.ExecuteStmt() ;
         }
      }

      private void TablesCount( )
      {
         if ( ! ReorganizationUtils.IsResumeMode( ) )
         {
            cmRGZ2 = connDefault.GetCommand("SELECT COUNT(*) FROM [Pais] ",false);
            cmRGZ2.ErrorMask = cmRGZ2.ErrorMask  |  ErrorMask.Lock;
            RGZ2 = cmRGZ2.FetchData();
            PaisCount = ( ( RGZ2.IsDBNull(0) )  ? (int)(0) : RGZ2.GetInt32(0) ) ;
            RGZ2.Close();
            ReorganizationUtils.PrintRecordCount ( "Pais" ,  PaisCount );
         }
      }

      private bool PreviousCheck( )
      {
         if ( ! ReorganizationUtils.IsResumeMode( ) )
         {
            if ( dsDefault.DBMSVersion( ) < 9 )
            {
               ReorganizationUtils.SetCheckError ( string.Format(resourceManager.GetString("GXM_bad_DBMS_version"), new   object[]  {"2005"}) );
               return false;
            }
         }
         if ( ! ReorganizationUtils.MustRunCheck( ) )
         {
            return true ;
         }
         if ( dsDefault.IsSqlServer2005( ) )
         {
            cmSELECT1 = connDefault.GetCommand("SELECT SCHEMA_NAME() ",true);
            cmSELECT1.ErrorMask = cmSELECT1.ErrorMask  |  ErrorMask.Lock;
            SELECT1 = cmSELECT1.FetchData();
            while ( cmSELECT1.HasMoreRows )
            {
               sSchemaVar = dsDefault.Db.GetString(SELECT1, 0, ref nsSchemaVar) ;
               cmSELECT1.HasMoreRows = SELECT1.Read();
            }
            SELECT1.Close();
         }
         else
         {
            cmSELECT2 = connDefault.GetCommand("SELECT USER_NAME() ",true);
            cmSELECT2.ErrorMask = cmSELECT2.ErrorMask  |  ErrorMask.Lock;
            SELECT2 = cmSELECT2.FetchData();
            while ( cmSELECT2.HasMoreRows )
            {
               sSchemaVar = dsDefault.Db.GetString(SELECT2, 0, ref nsSchemaVar) ;
               cmSELECT2.HasMoreRows = SELECT2.Read();
            }
            SELECT2.Close();
         }
         if ( ! TableExist("Pais",sSchemaVar) )
         {
            ReorganizationUtils.SetCheckError ( string.Format(resourceManager.GetString("GXM_table_not_exist"), new   object[]  {"Pais"}) );
            return false;
         }
         return true ;
      }

      private bool TableExist( String sTableName ,
                               String sMySchemaName )
      {
         bool result ;
         result = false ;
         cmINFORMATION_SCHEMA_TABLESSELECT1 = connDefault.GetCommand("SELECT [TABLE_NAME], [TABLE_SCHEMA] FROM INFORMATION_SCHEMA.TABLES WHERE ([TABLE_NAME] = @sTableName) AND ([TABLE_SCHEMA] = @sMySchemaName) ",true);
         if ( cmINFORMATION_SCHEMA_TABLESSELECT1.IDbCommand.Parameters.Count == 0 )
         {
            cmINFORMATION_SCHEMA_TABLESSELECT1.IDbCommand.Parameters.Add(  dsDefault.GetDbParameter( "@sTableName", System.Data.DbType.AnsiStringFixedLength,255));
            cmINFORMATION_SCHEMA_TABLESSELECT1.IDbCommand.Parameters.Add(  dsDefault.GetDbParameter( "@sMySchemaName", System.Data.DbType.AnsiStringFixedLength,255));
         }
         cmINFORMATION_SCHEMA_TABLESSELECT1.SetParameter(0, sTableName);
         cmINFORMATION_SCHEMA_TABLESSELECT1.SetParameter(1, sMySchemaName);
         cmINFORMATION_SCHEMA_TABLESSELECT1.ErrorMask = cmINFORMATION_SCHEMA_TABLESSELECT1.ErrorMask  |  ErrorMask.Lock;
         INFORMATION_SCHEMA_TABLESSELECT1 = cmINFORMATION_SCHEMA_TABLESSELECT1.FetchData();
         while ( cmINFORMATION_SCHEMA_TABLESSELECT1.HasMoreRows )
         {
            tablename = dsDefault.Db.GetString(INFORMATION_SCHEMA_TABLESSELECT1, 0, ref ntablename) ;
            schemaname = dsDefault.Db.GetString(INFORMATION_SCHEMA_TABLESSELECT1, 1, ref nschemaname) ;
            result = true ;
            cmINFORMATION_SCHEMA_TABLESSELECT1.HasMoreRows = INFORMATION_SCHEMA_TABLESSELECT1.Read();
         }
         INFORMATION_SCHEMA_TABLESSELECT1.Close();
         return result ;
      }

      private bool ColumnExist( String sTableName ,
                                String sMySchemaName ,
                                String sMyColumnName )
      {
         bool result ;
         result = false ;
         cmINFORMATION_SCHEMA_COLUMNSSELECT1 = connDefault.GetCommand("SELECT [TABLE_NAME], [TABLE_SCHEMA], [COLUMN_NAME] FROM INFORMATION_SCHEMA.COLUMNS WHERE ([TABLE_NAME] = @sTableName) AND ([TABLE_SCHEMA] = @sMySchemaName) AND ([COLUMN_NAME] = @sMyColumnName) ",true);
         if ( cmINFORMATION_SCHEMA_COLUMNSSELECT1.IDbCommand.Parameters.Count == 0 )
         {
            cmINFORMATION_SCHEMA_COLUMNSSELECT1.IDbCommand.Parameters.Add(  dsDefault.GetDbParameter( "@sTableName", System.Data.DbType.AnsiStringFixedLength,255));
            cmINFORMATION_SCHEMA_COLUMNSSELECT1.IDbCommand.Parameters.Add(  dsDefault.GetDbParameter( "@sMySchemaName", System.Data.DbType.AnsiStringFixedLength,255));
            cmINFORMATION_SCHEMA_COLUMNSSELECT1.IDbCommand.Parameters.Add(  dsDefault.GetDbParameter( "@sMyColumnName", System.Data.DbType.AnsiStringFixedLength,255));
         }
         cmINFORMATION_SCHEMA_COLUMNSSELECT1.SetParameter(0, sTableName);
         cmINFORMATION_SCHEMA_COLUMNSSELECT1.SetParameter(1, sMySchemaName);
         cmINFORMATION_SCHEMA_COLUMNSSELECT1.SetParameter(2, sMyColumnName);
         cmINFORMATION_SCHEMA_COLUMNSSELECT1.ErrorMask = cmINFORMATION_SCHEMA_COLUMNSSELECT1.ErrorMask  |  ErrorMask.Lock;
         INFORMATION_SCHEMA_COLUMNSSELECT1 = cmINFORMATION_SCHEMA_COLUMNSSELECT1.FetchData();
         while ( cmINFORMATION_SCHEMA_COLUMNSSELECT1.HasMoreRows )
         {
            tablename = dsDefault.Db.GetString(INFORMATION_SCHEMA_COLUMNSSELECT1, 0, ref ntablename) ;
            schemaname = dsDefault.Db.GetString(INFORMATION_SCHEMA_COLUMNSSELECT1, 1, ref nschemaname) ;
            columnname = dsDefault.Db.GetString(INFORMATION_SCHEMA_COLUMNSSELECT1, 2, ref ncolumnname) ;
            result = true ;
            cmINFORMATION_SCHEMA_COLUMNSSELECT1.HasMoreRows = INFORMATION_SCHEMA_COLUMNSSELECT1.Read();
         }
         INFORMATION_SCHEMA_COLUMNSSELECT1.Close();
         return result ;
      }

      private void ExecuteOnlyTablesReorganization( )
      {
         ReorganizePais( ) ;
      }

      private void ExecuteOnlyRisReorganization( )
      {
         RIClientePais( ) ;
         RICiudadPais( ) ;
      }

      private void ExecuteTablesReorganization( )
      {
         try
         {
            ReorganizationUtils.ExecBeforeReorg();
            ExecuteOnlyTablesReorganization( ) ;
            ExecuteOnlyRisReorganization( ) ;
            ReorganizationUtils.ExecAfterReorg();
         }
         catch ( Exception ex )
         {
            throw ex ;
         }
      }

      private void SetPrecedence( )
      {
         SetPrecedencetables( ) ;
         SetPrecedenceris( ) ;
      }

      private void SetPrecedencetables( )
      {
      }

      private void SetPrecedenceris( )
      {
      }

      private void ExecuteReorganization( )
      {
         if ( ErrCode == 0 )
         {
            TablesCount( ) ;
            if ( ! ReorganizationUtils.IsRecordCount( ) )
            {
               FirstActions( ) ;
               ExecuteTablesReorganization( ) ;
            }
         }
      }

      [System.Runtime.CompilerServices.MethodImpl(System.Runtime.CompilerServices.MethodImplOptions.Synchronized)]
      public void DropTableConstraints( String sTableName )
      {
         String cmdBuffer ;
         cmSYS_FOREIGN_KEYSSELECT1 = connDefault.GetCommand("SELECT OBJECT_NAME(object_id), OBJECT_NAME(parent_object_id), [referenced_object_id] FROM sys.foreign_keys WHERE [referenced_object_id] = OBJECT_ID(RTRIM(LTRIM(@sTableName))) ",true);
         if ( cmSYS_FOREIGN_KEYSSELECT1.IDbCommand.Parameters.Count == 0 )
         {
            cmSYS_FOREIGN_KEYSSELECT1.IDbCommand.Parameters.Add(  dsDefault.GetDbParameter( "@sTableName", System.Data.DbType.AnsiStringFixedLength,255));
         }
         cmSYS_FOREIGN_KEYSSELECT1.SetParameter(0, sTableName);
         cmSYS_FOREIGN_KEYSSELECT1.ErrorMask = cmSYS_FOREIGN_KEYSSELECT1.ErrorMask  |  ErrorMask.Lock;
         SYS_FOREIGN_KEYSSELECT1 = cmSYS_FOREIGN_KEYSSELECT1.FetchData();
         while ( cmSYS_FOREIGN_KEYSSELECT1.HasMoreRows )
         {
            constid = dsDefault.Db.GetString(SYS_FOREIGN_KEYSSELECT1, 0, ref nconstid) ;
            fkeyid = dsDefault.Db.GetString(SYS_FOREIGN_KEYSSELECT1, 1, ref nfkeyid) ;
            rkeyid = dsDefault.Db.GetInt32(SYS_FOREIGN_KEYSSELECT1, 2, ref nrkeyid) ;
            cmdBuffer = "ALTER TABLE " + "[" + fkeyid + "] DROP CONSTRAINT " + constid ;
            RGZ = connDefault.GetCommand(cmdBuffer,false);
            RGZ.IDbCommand.CommandTimeout = 0;
            RGZ.ExecuteStmt() ;
            cmSYS_FOREIGN_KEYSSELECT1.HasMoreRows = SYS_FOREIGN_KEYSSELECT1.Read();
         }
         SYS_FOREIGN_KEYSSELECT1.Close();
      }

      public void UtilsCleanup( )
      {
         this.Cleanup();
      }

      protected void Cleanup( )
      {
      }

      // Custom Exceptions
      public void Initialize( )
      {
         resourceManager = Deklarit.Utils.ResourceManager.Instance ;
         resourceManagerTables = new System.Resources.ResourceManager( "Deklarit.Tables", System.Reflection.Assembly.GetExecutingAssembly()) ;
         scmdbuf = "" ;
         sSchemaVar = "" ;
         sTableName = "" ;
         sMySchemaName = "" ;
         tablename = "" ;
         schemaname = "" ;
         sMyColumnName = "" ;
         columnname = "" ;
         constid = "" ;
         fkeyid = "" ;
         // GeneXus formulas.
      }

      protected short ErrCode ;
      protected int PaisCount ;
      protected int rkeyid ;
      protected String scmdbuf ;
      protected String sSchemaVar ;
      protected String sTableName ;
      protected String sMySchemaName ;
      protected String sMyColumnName ;
      protected bool nsSchemaVar ;
      protected bool ntablename ;
      protected bool nschemaname ;
      protected bool ncolumnname ;
      protected bool nconstid ;
      protected bool nfkeyid ;
      protected bool nrkeyid ;
      protected String tablename ;
      protected String schemaname ;
      protected String columnname ;
      protected String constid ;
      protected String fkeyid ;
      protected DataStore dsDefault ;
      protected System.Resources.ResourceManager resourceManager ;
      protected System.Resources.ResourceManager resourceManagerTables ;
      protected ReadWriteConnection connDefault ;
      protected ReadWriteCommand RGZ ;
      protected ReadWriteCommand cmRGZ2 ;
      protected IDataReader RGZ2 ;
      protected ReadWriteCommand cmSELECT1 ;
      protected IDataReader SELECT1 ;
      protected ReadWriteCommand cmSELECT2 ;
      protected IDataReader SELECT2 ;
      protected ReadWriteCommand cmINFORMATION_SCHEMA_TABLESSELECT1 ;
      protected IDataReader INFORMATION_SCHEMA_TABLESSELECT1 ;
      protected ReadWriteCommand cmINFORMATION_SCHEMA_COLUMNSSELECT1 ;
      protected IDataReader INFORMATION_SCHEMA_COLUMNSSELECT1 ;
      protected ReadWriteCommand cmSYS_FOREIGN_KEYSSELECT1 ;
      protected IDataReader SYS_FOREIGN_KEYSSELECT1 ;
   }

}
